<?php

declare(strict_types=1);

namespace app\extend;

use owns\enum\FileEnum;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * excel 导出报表简单封装
 * 例子：
 * $user = User::field('name,nickname,mobile,money,integral,create_time')->select()->toArray();
 * foreach($user as &$item) {
 *     $item['create_time'] = date('Y-m-d H:i', $item['create_time']);
 * }
 * $header = ['姓名','昵称','电话','余额','积分','注册时间'];
 * $sheet = new ExportExcelTool();
 * $sheet->setTitle('会员信息');
 * $sheet->setCellHorizontal('A1:G1');
 * $sheet->setColumnBackground('B', 'ca0c16');
 * $sheet->setHeader($header);
 * $sheet->setContent($user);
 * $res = $sheet->generateClsx('会员信息报表');
 * return success($res);
 */
class ExportExcel
{
    private $spreadsheet = null;
    private $row = 0;
    private $column = 0;
    private $sheet = null;

    // 列样式设置
    private $columnVertical = [];
    private $columnHorizontal = [];
    private $columnAlignment = [];
    private $columnFontBoldNnameSize = [];
    private $columnFontColor = [];
    private $columnBackground = [];

    public $sequence = true; // 默认开启表格序号

    // 单元格
    const TABLE_CELL = [
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O',
        'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC',
        'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO',
        'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
    ];

    public function __construct()
    {
        $this->spreadsheet = new Spreadsheet();
        $this->sheet = $this->spreadsheet->getActiveSheet(); // 激活工作表
    }
    /**
     * 获取工作表
     * @return Object
     */
    public function getSheet()
    {
        return $this->sheet;
    }

    /**
     * 设置表头
     * @param array $header eg: ['姓名', '性别', ...]
     */
    public function setHeader(array $header): void
    {
        $index = 0;
        $this->row = 1;
        if ($this->sequence) {
            $index++;
            $this->setStyles('A', $this->row);
            $this->sheet->getCell('A' . $this->row)->setValue('序号');
        }
        foreach ($header as $column) {
            $index++;
            $cell = self::TABLE_CELL[$index - 1] . $this->row;
            $this->setStyles(self::TABLE_CELL[$index - 1], $this->row);
            $this->sheet->getCell($cell)->setValue($column);
        }
        $this->column = $index;
    }
    /**
     * 设置内容
     * @param array $content
     */
    public function setContent(array &$content): void
    {
        $rowTag = $this->row == 1 ? 2 : 1;
        foreach ($content as $row) {
            if ($this->sequence) {
                $index = $this->sequence ? 1 : 0;
                $cell = self::TABLE_CELL[$index - 1] . $rowTag;
                $this->setStyles(self::TABLE_CELL[$index - 1], $rowTag);
                $this->sheet->getCell($cell)->setValue($this->row == 1 ? $rowTag - 1 : $rowTag);
            } else {
                $index = 0;
            }
            foreach ($row as $column) {
                $index++;
                $cell = self::TABLE_CELL[$index - 1] . $rowTag;
                $this->setStyles(self::TABLE_CELL[$index - 1], $rowTag);
                $this->sheet->getCell($cell)->setValue($column);
                if ($this->column && $this->column == $index) {
                    break;
                }
            }
            $rowTag++;
        }
    }
    /**
     * 生成clsx文件,返回文件路径
     * @param string $fileName 文件名
     */
    public function generateClsx(string $fileName = ''): string
    {
        $writer = new Xlsx($this->spreadsheet);
        $path = self::filePath($fileName);
        $writer->save($path['path']);
        return $path['url'];
    }
    /**
     * 设置标题
     * @param string $title 标题
     */
    public function setTitle(string $title): void
    {
        $this->sheet->setTitle($title);
    }
    /**
     * 【单元格】垂直居中
     * @param string $cell 单元格，eg: A1 / A1:C1
     */
    public function setCellVertical(string $cell): void
    {
        $this->sheet->getStyle($cell)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    }
    /**
     * 【列】垂直居中
     * @param string $column 列，eg: A / A:C
     */
    public function setColumnVertical(string $column): void
    {
        $this->columnVertical = self::cellSplit($column);
    }
    /**
     * 【单元格】水平居中
     * @param string $cell 单元格，eg: A1 / A1:C1
     */
    public function setCellHorizontal(string $cell): void
    {
        $this->sheet->getStyle($cell)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    }
    /**
     * 【列】垂直居中
     * @param string $column 列，eg: A / A:C
     */
    public function setColumnHorizontal(string $column): void
    {
        $this->columnHorizontal = self::cellSplit($column);
    }
    /**
     * 【单元格】设置文本字段换行，eg: A1 / A1:C1
     * @param string $cell
     */
    public function setCellAlignment(string $cell): void
    {
        $this->sheet->getStyle($cell)->getAlignment()->setWrapText(true);
    }
    /**
     * 【列】设置文本字段换行，eg: A1 / A1:C1
     * @param string $column 列，eg: A / A:C
     */
    public function setColumnAlignment(string $column): void
    {
        $this->columnAlignment = self::cellSplit($column);
    }
    /**
     * 【单元格】设置单元格字体 - 粗细、字体类型、字号
     * @param string $cell 单元格，eg: A1 / A1:C1
     * @param bool $bold 粗细，eg: true / false
     * @param string $name 字体: '宋体'
     * @param int $size 字号: 12
     */
    public function setCellFontBoldNnameSize(string $cell, bool $bold = false, string $name = '宋体', int $size = 12): void
    {
        $this->sheet->getStyle($cell)->getFont()->setBold($bold)->setName($name)->setSize($size);
    }
    /**
     * 【列】设置字体 - 粗细、字体类型、字号
     * @param string $column 列，eg: A1 / A1:C1
     * @param bool $bold 粗细，eg: true / false
     * @param string $name 字体: '宋体'
     * @param int $size 字号: 12
     */
    public function setColumnFontBoldNnameSize(string $column, bool $bold = false, string $name = '宋体', int $size = 12): void
    {
        $this->columnFontBoldNnameSize['column'] = self::cellSplit($column);
        $this->columnFontBoldNnameSize['bold'] = $bold;
        $this->columnFontBoldNnameSize['name'] = $name;
        $this->columnFontBoldNnameSize['size'] = $size;
    }
    /**
     * 【单元格】设置单元格字体颜色
     * @param string $cell 单元格，eg: A1 / A1:C1
     * @param string $color 颜色，eg: 000000
     */
    public function setCellFontColor(string $cell, string $color): void
    {
        $this->sheet->getStyle($cell)->getFont()->getColor()->setRGB($color);
    }
    /**
     * 【列】设置单元格字体颜色
     * @param string $column 列，eg: A / A:C
     * @param string $color 颜色，eg: 000000
     */
    public function setColumnFontColor(string $column, string $color): void
    {
        $this->columnFontColor['cell'] = self::cellSplit($column);
        $this->columnFontColor['color'] = $color;
    }
    /**
     * 【单元格】设置单元格背景色
     * @param string $cell 单元格，eg: A1 / A1:C1
     * @param string $color 颜色，eg: 000000
     */
    public function setCellBackground(string $cell, string $color): void
    {
        $this->sheet->getStyle($cell)->getFill()
            ->setFillType(Fill::FILL_SOLID) // 填充样式
            ->getStartColor()->setARGB($color);
    }
    /**
     * 【列】设置单元格背景色
     * @param string $column 列，eg: A / A:C
     * @param string $color 颜色，eg: 000000
     */
    public function setColumnBackground(string $column, string $color): void
    {
        $this->columnBackground['column'] = self::cellSplit($column);
        $this->columnBackground['color'] = $color;
    }
    /**
     * 【单元格】设置批注
     * @param string $cell 单元格，eg: A1
     * @param string $author
     */
    public function setCellAuthor(string $cell, string $author): void
    {
        $this->sheet->getComment($cell)->setAuthor($author);
    }
    /**
     * 样式
     * @param string $cell
     * @param int $index
     */
    private function setStyles($y, $x): void
    {
        if ($this->columnVertical && in_array($y, $this->columnVertical)) {
            $this->setCellVertical($y . $x);
        }
        if ($this->columnHorizontal && in_array($y, $this->columnHorizontal)) {
            $this->setCellHorizontal($y . $x);
        }
        if ($this->columnAlignment && in_array($y, $this->columnAlignment)) {
            $this->setCellAlignment($y . $x);
        }
        if ($this->columnFontBoldNnameSize && in_array($y, $this->columnFontBoldNnameSize['column'])) {
            $this->setColumnFontBoldNnameSize(
                $y . $x,
                $this->columnFontBoldNnameSize['bold'],
                $this->columnFontBoldNnameSize['name'],
                $this->columnFontBoldNnameSize['size']
            );
        }
        if ($this->columnFontColor && in_array($y, $this->columnFontColor['column'])) {
            $this->setCellFontColor($y . $x, $this->columnFontColor['color']);
        }
        if ($this->columnBackground && in_array($y, $this->columnBackground['column'])) {
            $this->setCellBackground($y . $x, $this->columnBackground['color']);
        }
    }
    /**
     * 保存设置
     */
    public static function filePath(string $fileName): array
    {
        list($path, $dirUrl) = make(FileEnum::class)->storageName('.xlsx', FileEnum::FILES, $fileName);
        return ['path' => $path, 'url' => $dirUrl];
    }
    /**
     * 拆分
     */
    public static function cellSplit($cell, $tag = ':'): array
    {
        $cells = explode($tag, $cell);
        if (count($cells) > 1) {
            $arrs = [];
            $interval = false;
            foreach (self::TABLE_CELL as $i) {
                if ($i == $cells[0]) {
                    $interval = true;
                }
                if ($interval) {
                    $arrs[] = $i;
                }
                if ($i == $cells[1]) {
                    break;
                }
            }
        } else {
            return $cells;
        }
    }
}
